﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProvinceCitySelect
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //连接串
            var connStr = ConfigurationManager.AppSettings["connStr"].ToString();

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"select * from AreaFull where AreaPID = 0";

                    //查询，使用SqlDataReader字段的读取和映射
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //将读取到该条记录，赋值给实体
                            AreaInfo obj = new AreaInfo();
                            obj.AreaName = reader["AreaName"].ToString();
                            obj.AreaID = int.Parse(reader["AreaID"].ToString());
                            obj.AreaPID = int.Parse(reader["AreaPID"].ToString());

                            //每读出一条记录就添加到下拉菜单中
                            cbxProvince.Items.Add(obj);
                        }
                    }
                }
            }
            //把下拉选项放到第1个
            cbxProvince.SelectedIndex = 0;
        }

        private void cbxProvince_SelectedIndexChanged(object sender, EventArgs e)
        {
            //按照当前选的省，查询出对应的城市，添加到cbxCity中
            //取出当前选项
            AreaInfo province = this.cbxProvince.SelectedItem as AreaInfo;
            //判断是否选择
            if (province == null)
                return;

            //按用户选择的areaid查询所有城市数据

            //连接串
            var connStr = ConfigurationManager.AppSettings["connStr"].ToString();

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"select * from AreaFull where AreaPID = " + province.AreaID;

                    //查询，使用SqlDataReader字段的读取和映射
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        //清除上次查询的结果
                        this.cbxCity.Items.Clear();
                        while (reader.Read())
                        {
                            AreaInfo obj = new AreaInfo();
                            obj.AreaName = reader["AreaName"].ToString();
                            obj.AreaID = int.Parse(reader["AreaID"].ToString());
                            obj.AreaPID = int.Parse(reader["AreaPID"].ToString());
                            this.cbxCity.Items.Add(obj);
                        }
                    }
                }
            }

            this.cbxCity.SelectedIndex = 0;
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            //选择保存文件
            string fileName = string.Empty;
            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                sfd.InitialDirectory = @"E:\1Rice\CSharpDataAccess";
                sfd.Filter = "文本文件|*.txt";

                if (sfd.ShowDialog() != DialogResult.OK)
                    return;

                //用户指定的文件名
                fileName = sfd.FileName;
            }

            //根据查询结果，写入数据到二进制文件
            //取出当前选项
            AreaInfo province = this.cbxProvince.SelectedItem as AreaInfo;
            //判断是否选择
            if (province == null)
                return;

            //连接串
            var connStr = ConfigurationManager.AppSettings["connStr"].ToString();

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"SELECT  (select top 1 a.areaname from areafull as a where a.areaid=areafull.areapid) as Province, AreaName FROM  AreaFull
                                                       where AreaPID = " + province.AreaID;

                    //查询，使用SqlDataReader字段的读取和映射
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        string tmp = string.Empty;
                        using (StreamWriter write = new StreamWriter(fileName))
                        {
                            while (reader.Read())
                            {
                                tmp = "省：" + reader["Province"].ToString() + "   城市：" + reader["AreaName"].ToString();
                                write.WriteLine(tmp);
                            }
                        }
                    }
                }
            }
        }
    }
}
